-- CREATE FOREIGN KEY NHANVIEN_PHG - PHONGBAN_MAPHG (1)
IF EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME='FK_NHANVIEN_PHONGBAN')
BEGIN
	ALTER TABLE NHANVIEN
	DROP CONSTRAINT FK_NHANVIEN_PHONGBAN
END
GO
ALTER TABLE NHANVIEN
ADD CONSTRAINT FK_NHANVIEN_PHONGBAN
FOREIGN KEY (PHG)
REFERENCES PHONGBAN(MAPHG)

--CREATE FOREIGN KEY PHANCONG_MA_NVIEN - NHANVIEN_MANV (2) 
IF EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME='FK_NHANVIEN_PHANCONG')
BEGIN
	ALTER TABLE NHANVIEN
	DROP CONSTRAINT FK_NHANVIEN_PHANCONG
END
GO
ALTER TABLE PHANCONG
ADD CONSTRAINT FK_NHANVIEN_PHANCONG
FOREIGN KEY (MA_NVIEN)
REFERENCES NHANVIEN(MANV)

-- CREATE FOREIGN KEY PHANCONG_SODA - DEAN_MADA (3)
IF EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME='FK_PHANCONG_DEAN')
BEGIN
	ALTER TABLE NHANVIEN
	DROP CONSTRAINT FK_PHANCONG_DEAN
END
GO
ALTER TABLE PHANCONG
ADD CONSTRAINT FK_PHANCONG_DEAN
FOREIGN KEY (SODA)
REFERENCES DEAN(MADA)

-- CREATE FOREIGN KEY THANNHAN_MA_NVIEN - NHANVIEN_MANV (4)
IF EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME='FK_THANNHAN_NHANVIEN')
BEGIN
	ALTER TABLE NHANVIEN
	DROP CONSTRAINT FK_THANNHAN_NHANVIEN
END
GO
ALTER TABLE THANNHAN
ADD CONSTRAINT FK_THANNHAN_NHANVIEN
FOREIGN KEY (MA_NVIEN)
REFERENCES NHANVIEN(MANV)

-- CREATE FOREIGN KEY DIADIEM_PHG_MAPHG - PHONGBAN_MAPHG (5)
IF EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME='FK_DIADIEM_PHG_PHONGBAN')
BEGIN
	ALTER TABLE NHANVIEN
	DROP CONSTRAINT FK_DIADIEM_PHG_PHONGBAN
END
GO
ALTER TABLE DIADIEM_PHG
ADD CONSTRAINT FK_DIADIEM_PHG_PHONGBAN
FOREIGN KEY	(MAPHG)
REFERENCES PHONGBAN(MAPHG)

-- CREATE FOREIGN KEY PHONGBAN_TRPHG - NHANVIEN_MANV (6)
IF EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME='FK_PHONGBAN_NHANVIEN')
BEGIN
	ALTER TABLE NHANVIEN
	DROP CONSTRAINT FK_PHONGBAN_NHANVIEN
END
GO
ALTER TABLE PHONGBAN
ADD CONSTRAINT FK_PHONGBAN_NHANVIEN
FOREIGN KEY (TRPHG)
REFERENCES NHANVIEN(MANV)